package com.jingbit.ai.dbase;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.provider.BaseColumns;

import com.jingbit.ai.entities.DBUserInfo;
import com.jingbit.ai.helper.FileHelper;
import com.jingbit.ai.helper.Logger;
import com.jingbit.ai.helper.StringHelper;

import java.io.File;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 数据库工具类
 *
 * @author pansha
 */
public class DBManager {
    private static final String TAG = "DBManager";
    private static final String DB_DEFAULT = "default.db";
    private static final String DB_REGIONS = "regions.db";

    private static final int DB_VERSION = 3;
    private static DBSqliteHelper sqliteHelper;
    private static SQLiteDatabase regionDBase;
    private static SQLiteDatabase readDBase;
    private static SQLiteDatabase writeDBase;
    private static DBManager dbManager;

    private DBManager() {
    }

    public static void initDBManager(Context context) {
        if (dbManager == null) {
            dbManager = new DBManager();
        }
    }

    public static void intSqliteHelper(Context context,int raw) {
    	installRawDBase(context,DB_REGIONS, raw);
        sqliteHelper = new DBSqliteHelper(context, DB_DEFAULT, null, DB_VERSION);
        readDBase = sqliteHelper.getReadableDatabase();
        writeDBase = sqliteHelper.getWritableDatabase();
    }

    /**
     * 安装本地数据库
     */
    private static void installRawDBase(Context context,String dbName,int dbRaw) {
        Logger.log(TAG, "installRawDBase",dbName,dbRaw);
        try {
            File dbFile = FileHelper.getDataFile(dbName);
            DBInstaller installer = new DBInstaller(context);
            installer.execute(dbFile,dbRaw);
        } catch (Exception e) {
            e.printStackTrace();
            Logger.log(TAG, "installRawDBase err",e.getMessage());
        }
    }

    public static void openOrCreate(File dbFile) {
        Logger.log(TAG, "openOrCreate", dbFile);
        if (dbFile != null) {
            regionDBase = SQLiteDatabase.openOrCreateDatabase(dbFile, null);
        }
    }

    public static SQLiteDatabase getRegionDBase() {
        return regionDBase;
    }

    public static final class UserColumns implements BaseColumns {
        public static final String USER_ID = "userId";
        public static final String USER_NAME = "userName";
        public static final String USER_PWD = "userPwd";
        public static final String USER_HEAD = "userHead";
        public static final String LAST_MODIFIED = "lastModified";
    }

    /**
     * 查询已登录过的所有用户
     */
    public static List<DBUserInfo> queryUsers() {
        Logger.log(TAG, "queryUsers");
        List<DBUserInfo> userInfos = new ArrayList<DBUserInfo>();
        String orderBy = UserColumns.LAST_MODIFIED + " desc";
        String sql = "select * from users order by " + orderBy;
        Cursor cursor = null;
        try {
            cursor = readDBase.rawQuery(sql, null);
            Logger.log(TAG, "queryUsers~", cursor.getCount());
            while (cursor.moveToNext()) {
                DBUserInfo user = new DBUserInfo();
                long uid = cursor.getLong(cursor.getColumnIndex(UserColumns.USER_ID));
                String uName = cursor.getString(cursor.getColumnIndex(UserColumns.USER_NAME));
                String uHead = cursor.getString(cursor.getColumnIndex(UserColumns.USER_HEAD));
                String password = cursor.getString(cursor.getColumnIndex(UserColumns.USER_PWD));
                user.setUserId(uid);
                user.setUserName(uName);
                user.setUserPhoto(uHead);
                user.setPassword(password);
                userInfos.add(user);
            }
            Logger.log(TAG, "queryUsers~", userInfos.size());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
           closeCursor(cursor);
        }
        return userInfos;
    }

    public static void insertUser(long userId, String userName, String userPwd, String userHead) {
        Logger.log(TAG, "insertUser", userId, userPwd, userHead);
        DBUserInfo userInfo = new DBUserInfo();
        userInfo.setUserId(userId);
        userInfo.setUserName(userName);
        userInfo.setUserPhoto(userHead);
        userInfo.setPassword(userPwd);
        userInfo.setLastModified(new Date().getTime());
        insertUser(userInfo);
    }

    /**
     * 保存注册用户
     *
     * @param userInfo
     */
    public static void insertUser(DBUserInfo userInfo) {
        boolean exist = existUser(userInfo.getUserName());
        Logger.log(TAG, "insertUser", userInfo + "/" + exist);
        if (exist) {
            updateLogin(userInfo);
            return;
        }
        try {
            String sql = "insert into users values(null,?,?,?,?,?)";
            writeDBase.beginTransaction(); // 开始事务
            writeDBase.execSQL(sql,
                    new Object[]{userInfo.getUserId(),
                            userInfo.getUserName(),
                            userInfo.getPassword(),
                            userInfo.getUserPhoto(),
                            new Date().getTime()});
            writeDBase.setTransactionSuccessful(); // 设置事务成功完成
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            writeDBase.endTransaction(); // 结束事务
        }
    }

    public static boolean existUser(String userName) {
        Logger.log(TAG, "existUser", userName);
        Cursor cursor = null;
        try {
            String sql = "select * from users where userName=?";
            cursor = readDBase.rawQuery(sql, new String[]{userName});
            if (cursor.moveToFirst()) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeCursor(cursor);
        }
        return false;
    }

    /**
     * 更新登录
     *
     * @param userInfo
     */
    public static int updateLogin(DBUserInfo userInfo) {
        Logger.log(TAG, "updateLogin", userInfo);
        if (userInfo == null) {
            return -1;
        }
        int i = 0;
        try {
            String where = UserColumns.USER_NAME + "=?";
            String[] whereArgs = new String[]{userInfo.getUserName()};
            ContentValues values = new ContentValues();
            values.put(UserColumns.USER_PWD, userInfo.getPassword());
            values.put(UserColumns.USER_HEAD, userInfo.getUserPhoto());
            values.put(UserColumns.LAST_MODIFIED, new Date().getTime());
            i = writeDBase.update("users", values, where, whereArgs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return i;
    }
    
    public static List<String> queryProvinces() {
		Logger.log(TAG, "queryProvinces", DBManager.getRegionDBase());
		List<String> regoinInfos = new ArrayList<String>();
		if (DBManager.getRegionDBase() == null) {
			return regoinInfos;
		}
		try {
			String sql = "select * from weather where countryCode='CN' group by provinceZh";
			Cursor cursor = DBManager.getRegionDBase().rawQuery(sql, null);
			return getRegionInfos(regoinInfos, cursor, true);
		} catch (Exception e) {
			e.printStackTrace();
			Logger.log(TAG, "queryProvinces err", e.getMessage());
		}
		return regoinInfos;
	}

	public static List<String> queryCities(String pro) {
		Logger.log(TAG, "queryCities",pro,DBManager.getRegionDBase());
		List<String> regoinInfos = new ArrayList<String>();
		if (DBManager.getRegionDBase() == null) {
			return regoinInfos;
		}
		try {
			String sql = "select * from weather where provinceZh=?";
			Cursor cursor = DBManager.getRegionDBase().rawQuery(sql, new String[]{pro});
			return getRegionInfos(regoinInfos, cursor, false);
		} catch (Exception e) {
			e.printStackTrace();
			Logger.log(TAG, "queryCities err", e.getMessage());
		}
		return regoinInfos;
	}


	private static List<String> getRegionInfos(List<String> regionInfos, Cursor cursor, boolean province) {
		Logger.log(TAG, "getRegionInfos", cursor.getCount(), cursor.getColumnCount());
		while (cursor.moveToNext()) {
			String city = null;
			if (province) {
				city = cursor.getString(cursor.getColumnIndex("provinceZh"));
			} else {
				city = cursor.getString(cursor.getColumnIndex("cityZh"));
			}
			if (!StringHelper.isChinese(city)) {
				continue;
			}
			regionInfos.add(city);
		}
		DBManager.closeCursor(cursor);
		return regionInfos;
	}


    public static void closeCursor(Cursor cursor) {
        if (cursor != null && !cursor.isClosed()) {
            cursor.close();
            cursor = null;
        }
    }

    public static void dispose() {
        try {
            if (regionDBase != null) {
                regionDBase.close();
                regionDBase = null;
            }
            if (writeDBase != null) {
                writeDBase.close();
                writeDBase = null;
            }
            if (readDBase != null) {
                readDBase.close();
                readDBase = null;
            }
            if (sqliteHelper != null) {
                sqliteHelper.close();
            }
        } catch (Exception e) {
            Logger.log(TAG, "dispose", e.getMessage());
        }

    }
}
